package com.oralce.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.amado.oracle.util.DBConnection;
import com.oralce.dao.UserDao;
import com.oralce.entity.UserEntity;

import oracle.jdbc.OracleTypes;

public class UserDaoImpl implements UserDao {

	@Override
	public void add(UserEntity userName) {
		Connection conn=Or.getConn();
		String prncedure="{call proc_lei_insert(?,?)}";
		CallableStatement cstmt=null;
		try {
			cstmt=conn.prepareCall(prncedure);
			cstmt.setString(1, userName.getUserName());
			cstmt.setString(2, userName.getPassword());
			cstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}DBConnection.close(null, cstmt, conn);
		
	}

	@Override
	public void delete(Long id) {
		Connection conn=Or.getConn();
		String prncedure="{call proc_lei_delete(?)}";
		CallableStatement cstmt = null;
		try {
			cstmt=conn.prepareCall(prncedure);
			cstmt.setLong(1, id);
			cstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBConnection.close(null, cstmt, conn);
	}

	@Override
	public void update(UserEntity userName) {
		Connection conn=Or.getConn();
		String prncedure="{call proc_lei_update(?)}";
		CallableStatement cstmt=null;
		try {
			cstmt=conn.prepareCall(prncedure);
			cstmt.setString(1, userName.getUserName());
			cstmt.setString(2, userName.getPassword());
			
			cstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public List<UserEntity> listAll() {
		Connection conn=Or.getConn();
		String prncedure ="{call proc_lei_select(?)}";
		CallableStatement cstmt;
		List<UserEntity> list = new ArrayList();
		try {
			cstmt=conn.prepareCall(prncedure);
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);// oracle驱动包里的类 import oracle.jdbc.OracleTypes;
			cstmt.execute();
			 ResultSet rs = (ResultSet) cstmt.getObject(1);
			while(rs.next()){
				UserEntity user=new UserEntity();
				user.setId(rs.getLong("id"));
				user.setUserName(rs.getString("user_Name"));
				user.setPassword(rs.getString("pass_word"));
				user.setCreationTime(rs.getTimestamp("creation_Time"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
	

	@Override
	public UserEntity getById(Long id) {
		Connection conn=Or.getConn();
		String prncedure="{call proc_LEI_GETBYId(?)}";
		List<UserEntity> list = new ArrayList();
		CallableStatement cstmt;
		try {
			cstmt=conn.prepareCall(prncedure);
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			 ResultSet rs = (ResultSet) cstmt.getObject(1);
			cstmt.execute();
			if(rs.next()) {
				UserEntity user=new UserEntity();
				
				user.setUserName(rs.getString("user_Name"));
				user.setPassword(rs.getString("pass_word"));
				user.setCreationTime(rs.getTimestamp("creation_Time"));
				list.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
